﻿using System;
using NazTek.Utility;
using System.Data.SqlClient;
using NazTek.Archive.Control.Properties;
using Microsoft.SqlServer.Management.Smo;
using NazTek.Utility.Extensions.SqlObject;

namespace NazTek.Archive.Control.Entity
{
    /// <summary>
    /// Encapsulates database table specific configuration information for archiving.
    /// </summary>
    [Serializable]
    public sealed class TableMetadata
    {
        /// <summary>
        /// Gets/sets source table name.
        /// </summary>
        public string Name { get; set; }
        /// <summary>
        /// Gets/sets source table schema.
        /// </summary>
        /// <remarks>Defaults to dbo, but maybe overridden in ArchiveConfiguration.xml.</remarks>
        public string Schema
        {
            get { return _schema; }
            set { _schema = value; }
        }
        /// <summary>
        /// Gets/sets name of the column that helps determine archive period.
        /// </summary>
        public string DateFieldName { get; set; }
        /// <summary>
        /// Gets/sets the number of days prior which to archive records.
        /// </summary>
        public int ArchivePeriodInDays { get; set; }
        /// <summary>
        /// Gets/sets archive query template.
        /// </summary>
        /// <remarks>Default template is provided, but maybe overridden in ArchiveConfiguration.xml.</remarks>
        public string ArchiveQueryTemplate
        {
            get { return _archiveQueryTemplate; }
            set { _archiveQueryTemplate = value; }
        }
        /// <summary>
        /// Gets/sets archive database name.
        /// </summary>
        /// <remarks>Default name is Archive, but maybe overridden in ArchiveConfiguration.xml.</remarks>
        public string ArchiveDatabaseName
        {
            get { return _archiveDatabaseName; }
            set { _archiveDatabaseName = value; }
        }
        /// <summary>
        /// Gets/sets archive table name.
        /// </summary>
        /// <remarks>Default name is &lt;source database name&gt;_&lt;source table name&gt;, but maybe overridden 
        /// in ArchiveConfiguration.xml.</remarks>
        public string ArchiveTableName
        {
            get { return _archiveTableName ?? GetArchiveTableName(Parent.Name); }
            set { _archiveTableName = value; }
        }

        /// <summary>
        /// Publishes notification.
        /// </summary>
        internal event EventHandler<NoticeEventArgs> Notice;
        /// <summary>
        /// Gets the archive table name, before prefix (parent name) is set.
        /// </summary>
        /// <param name="prefix"></param>
        /// <returns></returns>
        internal string GetArchiveTableName(string prefix)
        {
            return string.Format("{0}_{1}", prefix ?? Parent.Name, Name);
        }
        /// <summary>
        /// Archives records of <see cref="TableMetadata.Name">source table</see>, creating the archive table if necessary.
        /// </summary>
        /// <param name="parent">Archive source database.</param>
        internal void Archive(DatabaseMetadata parent)
        {
            Parent = parent;
            SourceDatabase = parent.DatabaseServer.Databases[parent.Name];
            ArchiveDatabase = parent.Parent.EnsureArchiveDatabase(ArchiveDatabaseName);
            EnsureTargetSchema();
            SetArchiveQuery();
            if (ArchiveDatabase.Tables.Contains(ArchiveTableName, Schema))
                SyncArchiveTableSchema();
            else
                CreateArchiveTable();
            TransferData();
        }

        /// <summary>
        /// Archive query template.
        /// </summary>
        private string _archiveQueryTemplate = Resources.ArchiveQueryTemplate;
        /// <summary>
        /// Archive schema.
        /// </summary>
        private string _schema = Utility.DefaultSchema;
        /// <summary>
        /// Archive database name.
        /// </summary>
        private string _archiveDatabaseName = Utility.DefaultArchiveDatabaseName;
        /// <summary>
        /// Archive table name.
        /// </summary>
        private string _archiveTableName;
        /// <summary>
        /// Gets/sets archive source database information.
        /// </summary>
        private DatabaseMetadata Parent { get; set; }
        /// <summary>
        /// Gets/sets archive query.
        /// </summary>
        private string ArchiveQuery { get; set; }
        /// <summary>
        /// Gets archive date by subtracting <see cref="ArchivePeriodInDays"/> from today.
        /// </summary>
        private DateTime ArchiveDate
        {
            get { return DateTime.Today.AddDays(ArchivePeriodInDays * -1); }
        }
        /// <summary>
        /// Gets/sets archive source database.
        /// </summary>
        private Database SourceDatabase { get; set; }
        /// <summary>
        /// Gets/sets archive database.
        /// </summary>
        private Database ArchiveDatabase { get; set; }
        /// <summary>
        /// Gets the database to archive to and creates target schema when missing.
        /// </summary>
        /// <returns>Archive database.</returns>
        private void EnsureTargetSchema()
        {
            if (!ArchiveDatabase.Schemas.Contains(Schema))
                new Schema(ArchiveDatabase, Schema).Create();
        }
        /// <summary>
        /// Sets archive query by setting values in <see cref="ArchiveQueryTemplate"/>.
        /// </summary>
        private void SetArchiveQuery()
        {
            var sourceTable = SourceDatabase.Tables[Name];
            var uniqueKey = sourceTable.GetUniqueKeyIndex();
            ArchiveQuery = string.Format(ArchiveQueryTemplate, uniqueKey.ColumnNamesWithTypes, uniqueKey.ColumnNames, 
                SourceDatabase.Name, Schema, Name, DateFieldName, ArchiveDate, ArchiveDatabase.Name, ArchiveTableName, 
                uniqueKey.TableJoinString, sourceTable.Columns.GetNames(null), sourceTable.Columns.GetNames("src"));
        }
        /// <summary>
        /// Create archive table in target archive database.
        /// </summary>
        private void CreateArchiveTable()
        {
            Notify(string.Format("Creating archive table: [{0}].[{1}].[{2}]",
                ArchiveDatabase.Name, Schema, ArchiveTableName));
            SourceDatabase.Tables[Name].CopyTo(ArchiveDatabase, ArchiveTableName, Notify);
        }
        /// <summary>
        /// Synchronizes two table schema and publishes client notification before the update.
        /// </summary>
        private void SyncArchiveTableSchema()
        {
            Notify(string.Format("Synchronizing archive table schema: [{3}].[{1}].[{4}] (from source [{0}].[{1}].[{2}])",
                Parent.Name, Schema, Name, ArchiveDatabaseName, ArchiveTableName));
            SourceDatabase.Tables[Name].Sync(ArchiveDatabase.Tables[ArchiveTableName], Notify);
        }
        /// <summary>
        /// Executes query to transfer data from source to archive table.
        /// </summary>
        private void TransferData()
        {
            var archiveServer = Parent.DatabaseServer;
            using (var connection = archiveServer.ConnectionContext.SqlConnectionObject)
            {
                connection.Open();
                using (var command = new SqlCommand(ArchiveQuery, connection))
                {
                    Notify(string.Format("Executing data transfer script [on connection ({0})]:\n{1}",
                        command.Connection.ConnectionString, command.CommandText));
                    var recordsMoved = command.ExecuteNonQuery();
                    Notify(string.Format("Number of records moved: {0}", recordsMoved/3));
                }
            }
        }
        /// <summary>
        /// Publishes notification, when there are active subscribers.
        /// </summary>
        /// <param name="message">Message to publish.</param>
        private void Notify(string message)
        {
            if (Notice != null)
                Notice(this, new NoticeEventArgs(message));
        }
    }
}